﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ReceiptDA : IReceiptDA
    {
        private string ADVICE_DATE = "@ADVICE_DATE";
        private string ADVICE_NUMBER = "@ADVICE_NUMBER";
        private string ALLOW_AUTO_RECEIPT = "@ALLOW_AUTO_RECEIPT";
        private string ARRIVAL_DATE = "@ARRIVAL_DATE";
        private string CARRIER_ADDRESS = "@CARRIER_ADDRESS";
        private string CARRIER_CITY = "@CARRIER_CITY";
        private string CARRIER_CONTACT = "@CARRIER_CONTACT";
        private string CARRIER_COUNTRY = "@CARRIER_COUNTRY";
        private string CARRIER_EMAIL = "@CARRIER_EMAIL";
        private string CARRIER_FAX = "@CARRIER_FAX";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CARRIER_NAME = "@CARRIER_NAME";
        private string CARRIER_PHONE = "@CARRIER_PHONE";
        private string CARRIER_PROV = "@CARRIER_PROV";
        private string CARRIER_REF = "@CARRIER_REF";
        private string CARRIER_ZIP = "@CARRIER_ZIP";
        private string CLOSED_DATE = "@CLOSED_DATE";
        private string CONTAINER_QTY = "@CONTAINER_QTY";
        private string CONTAINER_REF = "@CONTAINER_REF";
        private string CONTAINER_TYPE = "@CONTAINER_TYPE";
        private string CREATED_BY = "@CREATED_BY";
        private string DEST_COUNTRY = "@DEST_COUNTRY";
        private string DRIVER_NAME = "@DRIVER_NAME";
        private string EXPECTED_RECEIPT_DATE = "@EXPECTED_RECEIPT_DATE";
        private string EXTERNAL_RECEIPT_ID = "@EXTERNAL_RECEIPT_ID";
        private string EXTERNAL_RECEIPT_ID2 = "@EXTERNAL_RECEIPT_ID2";
        private string INCO_TERMS = "@INCO_TERMS";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string IS_PALLETIZED = "@IS_PALLETIZED";
        private string LOTTABLE_MATCH_REQUIRED = "@LOTTABLE_MATCH_REQUIRED";
        private string OPEN_QTY = "@OPEN_QTY";
        private string OWNER_ID = "@OWNER_ID";
        private string PACKING_SLIP_NUMBER = "@PACKING_SLIP_NUMBER";
        private string PAYMENT_TERM = "@PAYMENT_TERM";
        private string PLACE_OF_DELIVERY = "@PLACE_OF_DELIVERY";
        private string PLACE_OF_DISCHARGE = "@PLACE_OF_DISCHARGE";
        private string PLACE_OF_LOADING = "@PLACE_OF_LOADING";
        private string PO_ID = "@PO_ID";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string RECEIPT_TYPE = "@RECEIPT_TYPE";
        private string REMARK = "@REMARK";
        private string RMA = "@RMA";
        private string SQL_CHECK_RECEIPT_ID_UNIQUE = " SELECT COUNT(1) FROM @_@RECEIPT WHERE RECEIPT_ID = @RECEIPT_ID ";
        private string SQL_DELETE_RECEIPT = " DELETE FROM @_@RECEIPT WHERE RECEIPT_ID = @RECEIPT_ID ";
        private string SQL_INSERT_RECEIPT = " INSERT INTO @_@RECEIPT ( WH_ID, RECEIPT_ID, RECEIPT_TYPE, OWNER_ID, PO_ID, EXTERNAL_RECEIPT_ID, PACKING_SLIP_NUMBER, RMA, RECEIPT_DATE, EXPECTED_RECEIPT_DATE, CLOSED_DATE, OPEN_QTY, STATUS, EXTERNAL_RECEIPT_ID2, WAREHOUSE_REF, ARRIVAL_DATE, ADVICE_NUMBER, ADVICE_DATE, TRANSPORTATION_MODE, VENDOR_ID, VENDOR_NAME, VENDOR_ADDRESS, VENDOR_CITY, VENDOR_PROV, VENDOR_ZIP, VENDOR_COUNTRY, VENDOR_CONTACT, VENDOR_FAX, VENDOR_PHONE, VENDOR_EMAIL, VENDOR_REF, CARRIER_ID, CARRIER_NAME, CARRIER_ADDRESS, CARRIER_CITY, CARRIER_PROV, CARRIER_ZIP, CARRIER_COUNTRY, CARRIER_CONTACT, CARRIER_PHONE, CARRIER_FAX, CARRIER_EMAIL, CARRIER_REF, DRIVER_NAME, TRAILER_NUMBER, TRAILER_OWNER, SRC_COUNTRY, DEST_COUNTRY, VEHICLE_NUMBER, VEHICLE_DATE, PLACE_OF_LOADING, PLACE_OF_DISCHARGE, PLACE_OF_DELIVERY, INCO_TERMS, PAYMENT_TERM, CONTAINER_REF, CONTAINER_TYPE, CONTAINER_QTY, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, ALLOW_AUTO_RECEIPT, TRACK_INVENTORY_BY, LOTTABLE_MATCH_REQUIRED, IS_PALLETIZED, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @RECEIPT_ID, @RECEIPT_TYPE, @OWNER_ID, @PO_ID, @EXTERNAL_RECEIPT_ID, @PACKING_SLIP_NUMBER, @RMA, @RECEIPT_DATE, @EXPECTED_RECEIPT_DATE, @CLOSED_DATE, @OPEN_QTY, @STATUS, @EXTERNAL_RECEIPT_ID2, @WAREHOUSE_REF, @ARRIVAL_DATE, @ADVICE_NUMBER, @ADVICE_DATE, @TRANSPORTATION_MODE, @VENDOR_ID, @VENDOR_NAME, @VENDOR_ADDRESS, @VENDOR_CITY, @VENDOR_PROV, @VENDOR_ZIP, @VENDOR_COUNTRY, @VENDOR_CONTACT, @VENDOR_FAX, @VENDOR_PHONE, @VENDOR_EMAIL, @VENDOR_REF, @CARRIER_ID, @CARRIER_NAME, @CARRIER_ADDRESS, @CARRIER_CITY, @CARRIER_PROV, @CARRIER_ZIP, @CARRIER_COUNTRY, @CARRIER_CONTACT, @CARRIER_PHONE, @CARRIER_FAX, @CARRIER_EMAIL, @CARRIER_REF, @DRIVER_NAME, @TRAILER_NUMBER, @TRAILER_OWNER, @SRC_COUNTRY, @DEST_COUNTRY, @VEHICLE_NUMBER, @VEHICLE_DATE, @PLACE_OF_LOADING, @PLACE_OF_DISCHARGE, @PLACE_OF_DELIVERY, @INCO_TERMS, @PAYMENT_TERM, @CONTAINER_REF, @CONTAINER_TYPE, @CONTAINER_QTY, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @ALLOW_AUTO_RECEIPT, @TRACK_INVENTORY_BY, @LOTTABLE_MATCH_REQUIRED, @IS_PALLETIZED, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_RECEIPT = " SELECT  R.ROWID AS ROWID, R.WH_ID AS WH_ID, R.RECEIPT_ID AS RECEIPT_ID, T2.[TEXT] AS RECEIPT_TYPE_D,R.RECEIPT_TYPE AS RECEIPT_TYPE, R.OWNER_ID AS OWNER_ID, R.PO_ID AS PO_ID, R.EXTERNAL_RECEIPT_ID AS EXTERNAL_RECEIPT_ID, R.PACKING_SLIP_NUMBER AS PACKING_SLIP_NUMBER, R.RMA AS RMA, R.RECEIPT_DATE AS RECEIPT_DATE, R.EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE, R.CLOSED_DATE AS CLOSED_DATE, R.OPEN_QTY AS OPEN_QTY, T.[TEXT] AS STATUS_D,R.STATUS AS STATUS, R.EXTERNAL_RECEIPT_ID2 AS EXTERNAL_RECEIPT_ID2, R.WAREHOUSE_REF AS WAREHOUSE_REF, R.ARRIVAL_DATE AS ARRIVAL_DATE, R.ADVICE_NUMBER AS ADVICE_NUMBER, R.ADVICE_DATE AS ADVICE_DATE, R.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, R.VENDOR_ID AS VENDOR_ID, R.VENDOR_NAME AS VENDOR_NAME, R.VENDOR_ADDRESS AS VENDOR_ADDRESS, R.VENDOR_CITY AS VENDOR_CITY, R.VENDOR_PROV AS VENDOR_PROV, R.VENDOR_ZIP AS VENDOR_ZIP, R.VENDOR_COUNTRY AS VENDOR_COUNTRY, R.VENDOR_CONTACT AS VENDOR_CONTACT, R.VENDOR_FAX AS VENDOR_FAX, R.VENDOR_PHONE AS VENDOR_PHONE, R.VENDOR_EMAIL AS VENDOR_EMAIL, R.VENDOR_REF AS VENDOR_REF, R.CARRIER_ID AS CARRIER_ID, R.CARRIER_NAME AS CARRIER_NAME, R.CARRIER_ADDRESS AS CARRIER_ADDRESS, R.CARRIER_CITY AS CARRIER_CITY, R.CARRIER_PROV AS CARRIER_PROV, R.CARRIER_ZIP AS CARRIER_ZIP, R.CARRIER_COUNTRY AS CARRIER_COUNTRY, R.CARRIER_CONTACT AS CARRIER_CONTACT, R.CARRIER_PHONE AS CARRIER_PHONE, R.CARRIER_FAX AS CARRIER_FAX, R.CARRIER_EMAIL AS CARRIER_EMAIL, R.CARRIER_REF AS CARRIER_REF, R.DRIVER_NAME AS DRIVER_NAME, R.TRAILER_NUMBER AS TRAILER_NUMBER, R.TRAILER_OWNER AS TRAILER_OWNER, R.SRC_COUNTRY AS SRC_COUNTRY, R.DEST_COUNTRY AS DEST_COUNTRY, R.VEHICLE_NUMBER AS VEHICLE_NUMBER, R.VEHICLE_DATE AS VEHICLE_DATE, R.PLACE_OF_LOADING AS PLACE_OF_LOADING, R.PLACE_OF_DISCHARGE AS PLACE_OF_DISCHARGE, R.PLACE_OF_DELIVERY AS PLACE_OF_DELIVERY, R.INCO_TERMS AS INCO_TERMS, R.PAYMENT_TERM AS PAYMENT_TERM, R.CONTAINER_REF AS CONTAINER_REF, R.CONTAINER_TYPE AS CONTAINER_TYPE, R.CONTAINER_QTY AS CONTAINER_QTY, R.UDF1 AS UDF1, R.UDF2 AS UDF2, R.UDF3 AS UDF3, R.UDF4 AS UDF4, R.UDF5 AS UDF5, R.UDF6 AS UDF6, R.UDF7 AS UDF7, R.UDF8 AS UDF8, R.UDF9 AS UDF9, R.UDF10 AS UDF10, R.ALLOW_AUTO_RECEIPT AS ALLOW_AUTO_RECEIPT, R.TRACK_INVENTORY_BY AS TRACK_INVENTORY_BY, R.LOTTABLE_MATCH_REQUIRED AS LOTTABLE_MATCH_REQUIRED, R.IS_PALLETIZED AS IS_PALLETIZED, R.REMARK AS REMARK, R.IS_ACTIVE AS IS_ACTIVE, R.TS AS TS, R.CREATED_BY AS CREATED_BY, R.CREATED_DATE AS CREATED_DATE, R.UPDATED_BY AS UPDATED_BY, R.UPDATED_DATE AS UPDATED_DATE   FROM  @_@RECEIPT R  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = R.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+R.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_GROUP_BY = " SELECT  R.ROWID AS ROWID, R.WH_ID AS WH_ID, R.RECEIPT_ID AS RECEIPT_ID, T2.[TEXT] AS RECEIPT_TYPE_D,R.RECEIPT_TYPE AS RECEIPT_TYPE, R.OWNER_ID AS OWNER_ID, R.PO_ID AS PO_ID, R.EXTERNAL_RECEIPT_ID AS EXTERNAL_RECEIPT_ID, R.PACKING_SLIP_NUMBER AS PACKING_SLIP_NUMBER, R.RMA AS RMA, R.RECEIPT_DATE AS RECEIPT_DATE, R.EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE, R.CLOSED_DATE AS CLOSED_DATE, R.OPEN_QTY AS OPEN_QTY, T.[TEXT] AS STATUS_D,R.STATUS AS STATUS, R.EXTERNAL_RECEIPT_ID2 AS EXTERNAL_RECEIPT_ID2, R.WAREHOUSE_REF AS WAREHOUSE_REF, R.ARRIVAL_DATE AS ARRIVAL_DATE, R.ADVICE_NUMBER AS ADVICE_NUMBER, R.ADVICE_DATE AS ADVICE_DATE, R.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, R.VENDOR_ID AS VENDOR_ID, R.VENDOR_NAME AS VENDOR_NAME, R.VENDOR_ADDRESS AS VENDOR_ADDRESS, R.VENDOR_CITY AS VENDOR_CITY, R.VENDOR_PROV AS VENDOR_PROV, R.VENDOR_ZIP AS VENDOR_ZIP, R.VENDOR_COUNTRY AS VENDOR_COUNTRY, R.VENDOR_CONTACT AS VENDOR_CONTACT, R.VENDOR_FAX AS VENDOR_FAX, R.VENDOR_PHONE AS VENDOR_PHONE, R.VENDOR_EMAIL AS VENDOR_EMAIL, R.VENDOR_REF AS VENDOR_REF, R.CARRIER_ID AS CARRIER_ID, R.CARRIER_NAME AS CARRIER_NAME, R.CARRIER_ADDRESS AS CARRIER_ADDRESS, R.CARRIER_CITY AS CARRIER_CITY, R.CARRIER_PROV AS CARRIER_PROV, R.CARRIER_ZIP AS CARRIER_ZIP, R.CARRIER_COUNTRY AS CARRIER_COUNTRY, R.CARRIER_CONTACT AS CARRIER_CONTACT, R.CARRIER_PHONE AS CARRIER_PHONE, R.CARRIER_FAX AS CARRIER_FAX, R.CARRIER_EMAIL AS CARRIER_EMAIL, R.CARRIER_REF AS CARRIER_REF, R.DRIVER_NAME AS DRIVER_NAME, R.TRAILER_NUMBER AS TRAILER_NUMBER, R.TRAILER_OWNER AS TRAILER_OWNER, R.SRC_COUNTRY AS SRC_COUNTRY, R.DEST_COUNTRY AS DEST_COUNTRY, R.VEHICLE_NUMBER AS VEHICLE_NUMBER, R.VEHICLE_DATE AS VEHICLE_DATE, R.PLACE_OF_LOADING AS PLACE_OF_LOADING, R.PLACE_OF_DISCHARGE AS PLACE_OF_DISCHARGE, R.PLACE_OF_DELIVERY AS PLACE_OF_DELIVERY, R.INCO_TERMS AS INCO_TERMS, R.PAYMENT_TERM AS PAYMENT_TERM, R.CONTAINER_REF AS CONTAINER_REF, R.CONTAINER_TYPE AS CONTAINER_TYPE, R.CONTAINER_QTY AS CONTAINER_QTY, R.UDF1 AS UDF1, R.UDF2 AS UDF2, R.UDF3 AS UDF3, R.UDF4 AS UDF4, R.UDF5 AS UDF5, R.UDF6 AS UDF6, R.UDF7 AS UDF7, R.UDF8 AS UDF8, R.UDF9 AS UDF9, R.UDF10 AS UDF10, R.ALLOW_AUTO_RECEIPT AS ALLOW_AUTO_RECEIPT, R.TRACK_INVENTORY_BY AS TRACK_INVENTORY_BY, R.LOTTABLE_MATCH_REQUIRED AS LOTTABLE_MATCH_REQUIRED, R.IS_PALLETIZED AS IS_PALLETIZED, R.REMARK AS REMARK, R.IS_ACTIVE AS IS_ACTIVE, R.TS AS TS, R.CREATED_BY AS CREATED_BY, R.CREATED_DATE AS CREATED_DATE, R.UPDATED_BY AS UPDATED_BY, R.UPDATED_DATE AS UPDATED_DATE,SUM(RD.EXPECTED_QTY) AS EXPECTED_QTY,SUM(RD.RECEIVED_QTY) AS RECEIVED_QTY, SUM(RD.GROSS_WGT) AS GROSS_WGT,SUM(RD.CUBE)AS CUBE   FROM  @_@RECEIPT R  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = R.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+R.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN @_@RECEIPT_DETAIL RD ON R.RECEIPT_ID = RD.RECEIPT_ID ";
        private string SQL_UPDATE_RECEIPT = " UPDATE @_@RECEIPT SET WH_ID = @WH_ID, RECEIPT_TYPE = @RECEIPT_TYPE, OWNER_ID = @OWNER_ID, PO_ID = @PO_ID, EXTERNAL_RECEIPT_ID = @EXTERNAL_RECEIPT_ID, PACKING_SLIP_NUMBER = @PACKING_SLIP_NUMBER, RMA = @RMA, RECEIPT_DATE = @RECEIPT_DATE, EXPECTED_RECEIPT_DATE = @EXPECTED_RECEIPT_DATE, CLOSED_DATE = @CLOSED_DATE, OPEN_QTY = @OPEN_QTY, STATUS = @STATUS, EXTERNAL_RECEIPT_ID2 = @EXTERNAL_RECEIPT_ID2, WAREHOUSE_REF = @WAREHOUSE_REF, ARRIVAL_DATE = @ARRIVAL_DATE, ADVICE_NUMBER = @ADVICE_NUMBER, ADVICE_DATE = @ADVICE_DATE, TRANSPORTATION_MODE = @TRANSPORTATION_MODE, VENDOR_ID = @VENDOR_ID, VENDOR_NAME = @VENDOR_NAME, VENDOR_ADDRESS = @VENDOR_ADDRESS, VENDOR_CITY = @VENDOR_CITY, VENDOR_PROV = @VENDOR_PROV, VENDOR_ZIP = @VENDOR_ZIP, VENDOR_COUNTRY = @VENDOR_COUNTRY, VENDOR_CONTACT = @VENDOR_CONTACT, VENDOR_FAX = @VENDOR_FAX, VENDOR_PHONE = @VENDOR_PHONE, VENDOR_EMAIL = @VENDOR_EMAIL, VENDOR_REF = @VENDOR_REF, CARRIER_ID = @CARRIER_ID, CARRIER_NAME = @CARRIER_NAME, CARRIER_ADDRESS = @CARRIER_ADDRESS, CARRIER_CITY = @CARRIER_CITY, CARRIER_PROV = @CARRIER_PROV, CARRIER_ZIP = @CARRIER_ZIP, CARRIER_COUNTRY = @CARRIER_COUNTRY, CARRIER_CONTACT = @CARRIER_CONTACT, CARRIER_PHONE = @CARRIER_PHONE, CARRIER_FAX = @CARRIER_FAX, CARRIER_EMAIL = @CARRIER_EMAIL, CARRIER_REF = @CARRIER_REF, DRIVER_NAME = @DRIVER_NAME, TRAILER_NUMBER = @TRAILER_NUMBER, TRAILER_OWNER = @TRAILER_OWNER, SRC_COUNTRY = @SRC_COUNTRY, DEST_COUNTRY = @DEST_COUNTRY, VEHICLE_NUMBER = @VEHICLE_NUMBER, VEHICLE_DATE = @VEHICLE_DATE, PLACE_OF_LOADING = @PLACE_OF_LOADING, PLACE_OF_DISCHARGE = @PLACE_OF_DISCHARGE, PLACE_OF_DELIVERY = @PLACE_OF_DELIVERY, INCO_TERMS = @INCO_TERMS, PAYMENT_TERM = @PAYMENT_TERM, CONTAINER_REF = @CONTAINER_REF, CONTAINER_TYPE = @CONTAINER_TYPE, CONTAINER_QTY = @CONTAINER_QTY, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, ALLOW_AUTO_RECEIPT = @ALLOW_AUTO_RECEIPT, TRACK_INVENTORY_BY = @TRACK_INVENTORY_BY, LOTTABLE_MATCH_REQUIRED = @LOTTABLE_MATCH_REQUIRED, IS_PALLETIZED = @IS_PALLETIZED, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID  ";
        private string SQL_UPDATE_RECEIPT_STATUS = "UPDATE @_@RECEIPT SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID";
        private string SQL_UPDATE_RECEIPT_STATUS_AND_DATE = "UPDATE @_@RECEIPT SET STATUS = @STATUS, RECEIPT_DATE = @RECEIPT_DATE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID";
        private string SRC_COUNTRY = "@SRC_COUNTRY";
        private string STATUS = "@STATUS";
        private string TRACK_INVENTORY_BY = "@TRACK_INVENTORY_BY";
        private string TRAILER_NUMBER = "@TRAILER_NUMBER";
        private string TRAILER_OWNER = "@TRAILER_OWNER";
        private string TRANSPORTATION_MODE = "@TRANSPORTATION_MODE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VEHICLE_DATE = "@VEHICLE_DATE";
        private string VEHICLE_NUMBER = "@VEHICLE_NUMBER";
        private string VENDOR_ADDRESS = "@VENDOR_ADDRESS";
        private string VENDOR_CITY = "@VENDOR_CITY";
        private string VENDOR_CONTACT = "@VENDOR_CONTACT";
        private string VENDOR_COUNTRY = "@VENDOR_COUNTRY";
        private string VENDOR_EMAIL = "@VENDOR_EMAIL";
        private string VENDOR_FAX = "@VENDOR_FAX";
        private string VENDOR_ID = "@VENDOR_ID";
        private string VENDOR_NAME = "@VENDOR_NAME";
        private string VENDOR_PHONE = "@VENDOR_PHONE";
        private string VENDOR_PROV = "@VENDOR_PROV";
        private string VENDOR_REF = "@VENDOR_REF";
        private string VENDOR_ZIP = "@VENDOR_ZIP";
        private string WAREHOUSE_REF = "@WAREHOUSE_REF";
        private string WH_ID = "@WH_ID";

        public bool CheckReceiptIDUnique(string receiptID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_RECEIPT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckReceiptIDUnique(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_RECEIPT_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteReceipt(List<string> receiptID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < receiptID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_RECEIPT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteReceipt(DataBase dataBase, DbTransaction tran, List<string> receiptID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < receiptID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_RECEIPT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllReceipt(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllReceipt(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public ReceiptInfo GetReceiptByID(string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_GROUP_BY.Replace("@_@", whLoginID) + " WHERE R.RECEIPT_ID = @RECEIPT_ID  " + "GROUP BY  R.ROWID , R.WH_ID , R.RECEIPT_ID , T2.[TEXT] ,R.RECEIPT_TYPE , R.OWNER_ID , R.PO_ID, R.EXTERNAL_RECEIPT_ID , R.PACKING_SLIP_NUMBER , R.RMA , R.RECEIPT_DATE , R.EXPECTED_RECEIPT_DATE, R.CLOSED_DATE, R.OPEN_QTY, T.[TEXT],R.STATUS , R.EXTERNAL_RECEIPT_ID2, R.WAREHOUSE_REF , R.ARRIVAL_DATE , R.ADVICE_NUMBER , R.ADVICE_DATE , R.TRANSPORTATION_MODE , R.VENDOR_ID , R.VENDOR_NAME , R.VENDOR_ADDRESS , R.VENDOR_CITY , R.VENDOR_PROV , R.VENDOR_ZIP , R.VENDOR_COUNTRY , R.VENDOR_CONTACT , R.VENDOR_FAX , R.VENDOR_PHONE , R.VENDOR_EMAIL , R.VENDOR_REF , R.CARRIER_ID , R.CARRIER_NAME , R.CARRIER_ADDRESS , R.CARRIER_CITY , R.CARRIER_PROV , R.CARRIER_ZIP , R.CARRIER_COUNTRY , R.CARRIER_CONTACT , R.CARRIER_PHONE , R.CARRIER_FAX , R.CARRIER_EMAIL , R.CARRIER_REF , R.DRIVER_NAME , R.TRAILER_NUMBER , R.TRAILER_OWNER , R.SRC_COUNTRY , R.DEST_COUNTRY, R.VEHICLE_NUMBER , R.VEHICLE_DATE , R.PLACE_OF_LOADING , R.PLACE_OF_DISCHARGE, R.PLACE_OF_DELIVERY , R.INCO_TERMS , R.PAYMENT_TERM , R.CONTAINER_REF , R.CONTAINER_TYPE , R.CONTAINER_QTY , R.UDF1 , R.UDF2 , R.UDF3 , R.UDF4 , R.UDF5 , R.UDF6, R.UDF7 , R.UDF8 , R.UDF9 , R.UDF10 , R.ALLOW_AUTO_RECEIPT , R.TRACK_INVENTORY_BY , R.LOTTABLE_MATCH_REQUIRED , R.IS_PALLETIZED , R.REMARK , R.IS_ACTIVE , R.TS , R.CREATED_BY , R.CREATED_DATE , R.UPDATED_BY , R.UPDATED_DATE";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptInfo receiptInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    receiptInfo = new ReceiptInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_RECEIPT_ID"].ToString(), reader["PACKING_SLIP_NUMBER"].ToString(), reader["RMA"].ToString(), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["CLOSED_DATE"].ToString() != "") ? DateTime.Parse(reader["CLOSED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERNAL_RECEIPT_ID2"].ToString(), reader["WAREHOUSE_REF"].ToString(), (reader["ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["ARRIVAL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["ADVICE_NUMBER"].ToString(), (reader["ADVICE_DATE"].ToString() != "") ? DateTime.Parse(reader["ADVICE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRANSPORTATION_MODE"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["VENDOR_ADDRESS"].ToString(), reader["VENDOR_CITY"].ToString(), reader["VENDOR_PROV"].ToString(), reader["VENDOR_ZIP"].ToString(), reader["VENDOR_COUNTRY"].ToString(), reader["VENDOR_CONTACT"].ToString(), reader["VENDOR_FAX"].ToString(), reader["VENDOR_PHONE"].ToString(), reader["VENDOR_EMAIL"].ToString(), reader["VENDOR_REF"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_CONTACT"].ToString(), reader["CARRIER_PHONE"].ToString(), reader["CARRIER_FAX"].ToString(), reader["CARRIER_EMAIL"].ToString(), reader["CARRIER_REF"].ToString(), reader["DRIVER_NAME"].ToString(), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["SRC_COUNTRY"].ToString(), reader["DEST_COUNTRY"].ToString(), reader["VEHICLE_NUMBER"].ToString(), (reader["VEHICLE_DATE"].ToString() != "") ? DateTime.Parse(reader["VEHICLE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PLACE_OF_LOADING"].ToString(), reader["PLACE_OF_DISCHARGE"].ToString(), reader["PLACE_OF_DELIVERY"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_REF"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["ALLOW_AUTO_RECEIPT"].ToString(), reader["TRACK_INVENTORY_BY"].ToString(), reader["LOTTABLE_MATCH_REQUIRED"].ToString(), reader["IS_PALLETIZED"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f);
                }
            }
            return receiptInfo;
        }

        public ReceiptInfo GetReceiptByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID) + " WHERE R.RECEIPT_ID = @RECEIPT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptInfo receiptInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                receiptInfo = new ReceiptInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_RECEIPT_ID"].ToString(), reader["PACKING_SLIP_NUMBER"].ToString(), reader["RMA"].ToString(), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["CLOSED_DATE"].ToString() != "") ? DateTime.Parse(reader["CLOSED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERNAL_RECEIPT_ID2"].ToString(), reader["WAREHOUSE_REF"].ToString(), (reader["ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["ARRIVAL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["ADVICE_NUMBER"].ToString(), (reader["ADVICE_DATE"].ToString() != "") ? DateTime.Parse(reader["ADVICE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRANSPORTATION_MODE"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["VENDOR_ADDRESS"].ToString(), reader["VENDOR_CITY"].ToString(), reader["VENDOR_PROV"].ToString(), reader["VENDOR_ZIP"].ToString(), reader["VENDOR_COUNTRY"].ToString(), reader["VENDOR_CONTACT"].ToString(), reader["VENDOR_FAX"].ToString(), reader["VENDOR_PHONE"].ToString(), reader["VENDOR_EMAIL"].ToString(), reader["VENDOR_REF"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_CONTACT"].ToString(), reader["CARRIER_PHONE"].ToString(), reader["CARRIER_FAX"].ToString(), reader["CARRIER_EMAIL"].ToString(), reader["CARRIER_REF"].ToString(), reader["DRIVER_NAME"].ToString(), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["SRC_COUNTRY"].ToString(), reader["DEST_COUNTRY"].ToString(), reader["VEHICLE_NUMBER"].ToString(), (reader["VEHICLE_DATE"].ToString() != "") ? DateTime.Parse(reader["VEHICLE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PLACE_OF_LOADING"].ToString(), reader["PLACE_OF_DISCHARGE"].ToString(), reader["PLACE_OF_DELIVERY"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_REF"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["ALLOW_AUTO_RECEIPT"].ToString(), reader["TRACK_INVENTORY_BY"].ToString(), reader["LOTTABLE_MATCH_REQUIRED"].ToString(), reader["IS_PALLETIZED"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptInfo;
        }

        public ReceiptInfo GetReceiptByPoID(string PoID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID) + " WHERE R.PO_ID = @PO_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", PoID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptInfo receiptInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    receiptInfo = new ReceiptInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_RECEIPT_ID"].ToString(), reader["PACKING_SLIP_NUMBER"].ToString(), reader["RMA"].ToString(), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["CLOSED_DATE"].ToString() != "") ? DateTime.Parse(reader["CLOSED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERNAL_RECEIPT_ID2"].ToString(), reader["WAREHOUSE_REF"].ToString(), (reader["ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["ARRIVAL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["ADVICE_NUMBER"].ToString(), (reader["ADVICE_DATE"].ToString() != "") ? DateTime.Parse(reader["ADVICE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRANSPORTATION_MODE"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["VENDOR_ADDRESS"].ToString(), reader["VENDOR_CITY"].ToString(), reader["VENDOR_PROV"].ToString(), reader["VENDOR_ZIP"].ToString(), reader["VENDOR_COUNTRY"].ToString(), reader["VENDOR_CONTACT"].ToString(), reader["VENDOR_FAX"].ToString(), reader["VENDOR_PHONE"].ToString(), reader["VENDOR_EMAIL"].ToString(), reader["VENDOR_REF"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_CONTACT"].ToString(), reader["CARRIER_PHONE"].ToString(), reader["CARRIER_FAX"].ToString(), reader["CARRIER_EMAIL"].ToString(), reader["CARRIER_REF"].ToString(), reader["DRIVER_NAME"].ToString(), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["SRC_COUNTRY"].ToString(), reader["DEST_COUNTRY"].ToString(), reader["VEHICLE_NUMBER"].ToString(), (reader["VEHICLE_DATE"].ToString() != "") ? DateTime.Parse(reader["VEHICLE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PLACE_OF_LOADING"].ToString(), reader["PLACE_OF_DISCHARGE"].ToString(), reader["PLACE_OF_DELIVERY"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_REF"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["ALLOW_AUTO_RECEIPT"].ToString(), reader["TRACK_INVENTORY_BY"].ToString(), reader["LOTTABLE_MATCH_REQUIRED"].ToString(), reader["IS_PALLETIZED"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return receiptInfo;
        }

        public DataSet GetReceiptByQueryList(List<string> sqlWhere, ReceiptQueryEntity receiptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!receiptQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, receiptQuery.CurrentPage, receiptQuery.PageSize, receiptQuery.SortField, receiptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetReceiptByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ReceiptQueryEntity receiptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!receiptQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, receiptQuery.CurrentPage, receiptQuery.PageSize, receiptQuery.SortField, receiptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertReceipt(ReceiptInfo receiptInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                receiptInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Receipt_Parameters(receiptInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertReceipt(DataBase dataBase, DbTransaction tran, ReceiptInfo receiptInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                receiptInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Receipt_Parameters(receiptInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Receipt_Parameters(ReceiptInfo receiptInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_RECEIPT_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PACKING_SLIP_NUMBER, SqlDbType.VarChar, 50), new SqlParameter(this.RMA, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECTED_RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.CLOSED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_RECEIPT_ID2, SqlDbType.VarChar, 20), new SqlParameter(this.WAREHOUSE_REF, SqlDbType.VarChar, 20), new SqlParameter(this.ARRIVAL_DATE, SqlDbType.DateTime, 8), 
                new SqlParameter(this.ADVICE_NUMBER, SqlDbType.VarChar, 30), new SqlParameter(this.ADVICE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.TRANSPORTATION_MODE, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.VENDOR_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_FAX, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_EMAIL, SqlDbType.VarChar, 60), new SqlParameter(this.VENDOR_REF, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.CARRIER_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CARRIER_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_FAX, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_EMAIL, SqlDbType.VarChar, 60), new SqlParameter(this.CARRIER_REF, SqlDbType.VarChar, 20), new SqlParameter(this.DRIVER_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.TRAILER_NUMBER, SqlDbType.VarChar, 30), new SqlParameter(this.TRAILER_OWNER, SqlDbType.VarChar, 30), new SqlParameter(this.SRC_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.DEST_COUNTRY, SqlDbType.VarChar, 30), 
                new SqlParameter(this.VEHICLE_NUMBER, SqlDbType.VarChar, 20), new SqlParameter(this.VEHICLE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.PLACE_OF_LOADING, SqlDbType.VarChar, 500), new SqlParameter(this.PLACE_OF_DISCHARGE, SqlDbType.VarChar, 500), new SqlParameter(this.PLACE_OF_DELIVERY, SqlDbType.VarChar, 500), new SqlParameter(this.INCO_TERMS, SqlDbType.VarChar, 20), new SqlParameter(this.PAYMENT_TERM, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_REF, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.ALLOW_AUTO_RECEIPT, SqlDbType.VarChar, 1), new SqlParameter(this.TRACK_INVENTORY_BY, SqlDbType.VarChar, 1), new SqlParameter(this.LOTTABLE_MATCH_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.IS_PALLETIZED, SqlDbType.VarChar, 1), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(receiptInfo.WhID))
            {
                paramArray[0].Value = receiptInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = receiptInfo.ReceiptID;
            paramArray[2].Value = receiptInfo.ReceiptType;
            paramArray[3].Value = receiptInfo.OwnerID;
            if (!string.IsNullOrEmpty(receiptInfo.PoID))
            {
                paramArray[4].Value = receiptInfo.PoID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = receiptInfo.ExternalReceiptID;
            if (!string.IsNullOrEmpty(receiptInfo.PackingSlipNumber))
            {
                paramArray[6].Value = receiptInfo.PackingSlipNumber;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Rma))
            {
                paramArray[7].Value = receiptInfo.Rma;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (receiptInfo.ReceiptDate != CS00003)
            {
                paramArray[8].Value = receiptInfo.ReceiptDate;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = receiptInfo.ExpectedReceiptDate;
            CS00003 = new DateTime();
            if (receiptInfo.ClosedDate != CS00003)
            {
                paramArray[10].Value = receiptInfo.ClosedDate;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            paramArray[11].Value = receiptInfo.OpenQty;
            paramArray[12].Value = receiptInfo.Status;
            if (!string.IsNullOrEmpty(receiptInfo.ExternalReceiptId2))
            {
                paramArray[13].Value = receiptInfo.ExternalReceiptId2;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.WarehouseRef))
            {
                paramArray[14].Value = receiptInfo.WarehouseRef;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = receiptInfo.ArrivalDate;
            if (!string.IsNullOrEmpty(receiptInfo.AdviceNumber))
            {
                paramArray[0x10].Value = receiptInfo.AdviceNumber;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (receiptInfo.AdviceDate != CS00003)
            {
                paramArray[0x11].Value = receiptInfo.AdviceDate;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.TransportationMode))
            {
                paramArray[0x12].Value = receiptInfo.TransportationMode;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorID))
            {
                paramArray[0x13].Value = receiptInfo.VendorID;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorName))
            {
                paramArray[20].Value = receiptInfo.VendorName;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorAddress))
            {
                paramArray[0x15].Value = receiptInfo.VendorAddress;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorCity))
            {
                paramArray[0x16].Value = receiptInfo.VendorCity;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorProv))
            {
                paramArray[0x17].Value = receiptInfo.VendorProv;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorZip))
            {
                paramArray[0x18].Value = receiptInfo.VendorZip;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorCountry))
            {
                paramArray[0x19].Value = receiptInfo.VendorCountry;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorContact))
            {
                paramArray[0x1a].Value = receiptInfo.VendorContact;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorFax))
            {
                paramArray[0x1b].Value = receiptInfo.VendorFax;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorPhone))
            {
                paramArray[0x1c].Value = receiptInfo.VendorPhone;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorEmail))
            {
                paramArray[0x1d].Value = receiptInfo.VendorEmail;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VendorRef))
            {
                paramArray[30].Value = receiptInfo.VendorRef;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierID))
            {
                paramArray[0x1f].Value = receiptInfo.CarrierID;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierName))
            {
                paramArray[0x20].Value = receiptInfo.CarrierName;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierAddress))
            {
                paramArray[0x21].Value = receiptInfo.CarrierAddress;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierCity))
            {
                paramArray[0x22].Value = receiptInfo.CarrierCity;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierProv))
            {
                paramArray[0x23].Value = receiptInfo.CarrierProv;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierZip))
            {
                paramArray[0x24].Value = receiptInfo.CarrierZip;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierCountry))
            {
                paramArray[0x25].Value = receiptInfo.CarrierCountry;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierContact))
            {
                paramArray[0x26].Value = receiptInfo.CarrierContact;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierPhone))
            {
                paramArray[0x27].Value = receiptInfo.CarrierPhone;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierFax))
            {
                paramArray[40].Value = receiptInfo.CarrierFax;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierEmail))
            {
                paramArray[0x29].Value = receiptInfo.CarrierEmail;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.CarrierRef))
            {
                paramArray[0x2a].Value = receiptInfo.CarrierRef;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.DriverName))
            {
                paramArray[0x2b].Value = receiptInfo.DriverName;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.TrailerNumber))
            {
                paramArray[0x2c].Value = receiptInfo.TrailerNumber;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.TrailerOwner))
            {
                paramArray[0x2d].Value = receiptInfo.TrailerOwner;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.SrcCountry))
            {
                paramArray[0x2e].Value = receiptInfo.SrcCountry;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.DestCountry))
            {
                paramArray[0x2f].Value = receiptInfo.DestCountry;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.VehicleNumber))
            {
                paramArray[0x30].Value = receiptInfo.VehicleNumber;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (receiptInfo.VehicleDate != new DateTime())
            {
                paramArray[0x31].Value = receiptInfo.VehicleDate;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.PlaceOfLoading))
            {
                paramArray[50].Value = receiptInfo.PlaceOfLoading;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.PlaceOfDischarge))
            {
                paramArray[0x33].Value = receiptInfo.PlaceOfDischarge;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.PlaceOfDelivery))
            {
                paramArray[0x34].Value = receiptInfo.PlaceOfDelivery;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.IncoTerms))
            {
                paramArray[0x35].Value = receiptInfo.IncoTerms;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.PaymentTerm))
            {
                paramArray[0x36].Value = receiptInfo.PaymentTerm;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.ContainerRef))
            {
                paramArray[0x37].Value = receiptInfo.ContainerRef;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.ContainerType))
            {
                paramArray[0x38].Value = receiptInfo.ContainerType;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            paramArray[0x39].Value = receiptInfo.ContainerQty;
            if (!string.IsNullOrEmpty(receiptInfo.Udf1))
            {
                paramArray[0x3a].Value = receiptInfo.Udf1;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf2))
            {
                paramArray[0x3b].Value = receiptInfo.Udf2;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf3))
            {
                paramArray[60].Value = receiptInfo.Udf3;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf4))
            {
                paramArray[0x3d].Value = receiptInfo.Udf4;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf5))
            {
                paramArray[0x3e].Value = receiptInfo.Udf5;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf6))
            {
                paramArray[0x3f].Value = receiptInfo.Udf6;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf7))
            {
                paramArray[0x40].Value = receiptInfo.Udf7;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf8))
            {
                paramArray[0x41].Value = receiptInfo.Udf8;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf9))
            {
                paramArray[0x42].Value = receiptInfo.Udf9;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.Udf10))
            {
                paramArray[0x43].Value = receiptInfo.Udf10;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.AllowAutoReceipt))
            {
                paramArray[0x44].Value = receiptInfo.AllowAutoReceipt;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptInfo.TrackInventoryBy))
            {
                paramArray[0x45].Value = receiptInfo.TrackInventoryBy;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            paramArray[70].Value = receiptInfo.LottableMatchRequired;
            paramArray[0x47].Value = receiptInfo.IsPalletized;
            if (!string.IsNullOrEmpty(receiptInfo.Remark))
            {
                paramArray[0x48].Value = receiptInfo.Remark;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            paramArray[0x49].Value = receiptInfo.IsActive;
            paramArray[0x4a].Value = receiptInfo.CreatedBy;
            paramArray[0x4b].Value = receiptInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateReceipt(ReceiptInfo receiptInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                receiptInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Receipt_Parameters(receiptInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceipt(DataBase dataBase, DbTransaction tran, ReceiptInfo receiptInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                receiptInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Receipt_Parameters(receiptInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RECEIPT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptStatus(string receiptID, string status, string whLoginID, string updatedBy)
        {
            string sql = this.SQL_UPDATE_RECEIPT_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public int UpdateReceiptStatus(DataBase dataBase, DbTransaction tran, string receiptID, string status, string whLoginID, string updatedBy)
        {
            string sql = this.SQL_UPDATE_RECEIPT_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public int UpdateReceiptStatusAndDate(string receiptID, string status, DateTime receiptDate, string whLoginID, string updatedBy)
        {
            string sql = this.SQL_UPDATE_RECEIPT_STATUS_AND_DATE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@RECEIPT_DATE", receiptDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public int UpdateReceiptStatusAndDate(DataBase dataBase, DbTransaction tran, string receiptID, DateTime receiptDate, string status, string whLoginID, string updatedBy)
        {
            string sql = this.SQL_UPDATE_RECEIPT_STATUS_AND_DATE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@RECEIPT_DATE", receiptDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }
    }
}

